-- create language plpgsql;

create temp table xv as (
select "ID",  "TAG" from "CaptureData"
);

-------------------------------[ fun_tab2fasta ]-------------------------------
--
-- Converts a two-column table of sequences and sequence names to fasta format
--
-- -------------------------------[ Input ]------------------------------------
-- a SELECT QUERY returning two columns: names (first col) and 
-- sequences (2nd column). Column names don't matter but order does.
-- 
-- -------------------------------[ Usage ]------------------------------------
-- SELECT * FROM fun_tab2fasta('SELECT seq_name, seq FROM mytable');
-- 
-- Or to output to file:
-- COPY (
--   SELECT * FROM fun_tab2fasta('SELECT seq_name, sequence FROM mytable')
--   ) TO '/tmp/tmp.fasta';
-------------------------------------------------------------------------------

create or replace function fun_tab2fasta(fasta_table text) returns SETOF varchar AS $$

  BEGIN
  -- Create fasta tbl to store input query
  create temp table fasta (
    fasta_name varchar(256),
    fasta_sequence varchar(10000),
    fasta_order SERIAL NOT NULL -- This to keep names and seqs in right order
    );
    
  EXECUTE
  -- Dump input query into fasta
    'INSERT INTO fasta ' || fasta_table;

  -- Make valid fasta name
  update fasta set fasta_name = '>' || fasta_name;

  -- Stack column of names on top of column sequences
  create temp table fasta2 AS(
    select fasta_name, fasta_order from fasta
    union select fasta_sequence, fasta_order from fasta
    );

  -- Put names and sequences in the right order
  create temp table fasta3 AS (
    select fasta_name from fasta2
    order by fasta_order
    );

  -- Return table
  return query select * from fasta3;

  -- Tidy up temp tables
  drop table fasta;
  drop table fasta2;
  drop table fasta3;
  
  END;  
$$ language plpgsql;

copy (
select * from funtmp('select * from xv')
  ) to '/tmp/tmp.fasta';

select * from fasta;
select * from fasta2;
select * from fasta3;